package com.dc.free.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;

/**
 * excel处理
 * @author quan
 *
 */
public class ExcelUtil {
	
	public static ExcelUtil me = new ExcelUtil();

	/**
	 * 读取excel
	 * @param workbook
	 * @return
	 * @throws IOException
	 */
	public List<List<List<String>>> read(Workbook workbook) throws IOException {
		List<List<List<String>>> excelResult = new ArrayList<List<List<String>>>();
		List<List<String>> sheetResult = null;
		
		//循环每一页，并处理当前循环页
		for(int sheetNum=0; sheetNum<workbook.getNumberOfSheets(); sheetNum++) {
			Sheet sheet = workbook.getSheetAt(sheetNum);
			sheetResult = new ArrayList<List<String>>();
			if(sheet == null) continue;
			
			//处理当前页，循环读取每一行
			for(int rowNum=0; rowNum<sheet.getLastRowNum(); rowNum++) {
				Row row = sheet.getRow(rowNum);
				//去除空行
				if(row == null) continue;
				int minCellX = row.getFirstCellNum();
				int maxCellX = row.getLastCellNum();
				List<String> rowList = new ArrayList<String>();
				
				//遍历该行，获取每个cell元素
				for(int cellX=minCellX; cellX<maxCellX; cellX++) {
					Cell cell = row.getCell(cellX);
					if(cell == null) continue;
					rowList.add(cell.toString());
				}
				
				sheetResult.add(rowList);
			}
			
			excelResult.add(sheetResult);
		}
		
		return excelResult;
	}
	
	public List<List<List<String>>> read(String path) throws IOException {
		Workbook workbook = null;
		
		if(path.endsWith(".xls"))  workbook = new HSSFWorkbook(new FileInputStream(path));
		if(path.endsWith(".xlsx"))  workbook = new XSSFWorkbook(new FileInputStream(path));
		return read(workbook);
	}
	
	public List<List<List<String>>> read(File file) throws IOException {
		Workbook workbook = null;
		String fileName = file.getName(); 
		
		if(fileName.endsWith(".xls"))  workbook = new HSSFWorkbook(new FileInputStream(file));
		if(fileName.endsWith(".xlsx"))  workbook = new XSSFWorkbook(new FileInputStream(file));
		return read(workbook);
	}
	
	/**
	 * 二维数组写到excel
	 * @param arr 二维数组
	 * @return
	 */
	public Workbook write(JSONArray arr) {
		Workbook workbook = new HSSFWorkbook();
		Sheet sheet = workbook.createSheet();
		Row row = null;
		JSONArray arrRow = null;
		
		//遍历数组，写到单元格
		for(int i=0; i<arr.size(); i++) {
			arrRow = arr.getJSONArray(i);
			row = sheet.createRow(i);
			for(int j=0; j<arrRow.size(); j++) {
				row.createCell(j).setCellValue(arrRow.getString(j));
			}
		}
		
		return workbook;
	}
	
	public static void main(String[] args) throws IOException {
		/*ExcelUtil excel = new ExcelUtil();
		List<List<List<String>>> list = excel.read("C:/Users/quan/Desktop/1.xls");*/
		/*for(List<String> l: list) {

			System.out.println(JSON.toJSONString(l));

		}*/
		JSONArray arr = JSON.parseArray("[[1,2], [3,4,5],[6,7],[8]]");
		ExcelUtil.me.write(arr).write(new FileOutputStream("E:\\11.xls"));
		
	}
}